
ALTER  proc sp_change_sales_order_proc

@SO_Customer_PO_Number 	varchar(50),
@SO_Contact_Person 		varchar(50),
@SO_Sales_Person 		varchar(50),
@SO_Shipping_Method 		varchar(50),
@SO_Shipping_Account 		varchar(50),
@SO_Shipping_Address_ID 	int,
@SO_Comments 			varchar(500),
@SO_Shipping_Instructions 	varchar(500),
@SO_EndUser 			varchar(50),
@SO_Sales_Tax_Type 		varchar(50),
@SO_Sub_Total 			float,
@SO_Sales_Tax 			float,
@SO_Frieght_Tax 		float,
@SO_Misc 			float,
@SalesOrder_No 			varchar(50),
@Regional_Rep 			varchar(50),
@Shipping_Terms 		varchar(50),
@Delivery_Terms 		varchar(50),
@SO_Booking_date 		datetime,
@salesorder_id			int,
@CSO_Reason			varchar(50),
@Phone				varchar(50),
@Fax				varchar(50),
@Email				varchar(50),
@CellNumber			varchar(50), 
@Address			varchar(50),
@Status_Flag 			int output,
@miscName			varchar(30),
@SO_Billing_Address_ID		int,         
@SO_Billing_Contact_Person 	varchar(50), 
@SO_Shipping_Contact_Person 	varchar(50), 
@SO_Sales_Rep			varchar(50), 
@SO_PO_Rev			varchar(50), 
@SO_PO_Date			datetime,    
@SO_ODM_ID                     	int,         
@SO_Is_Customer_Ship_Address   	bit,
@SO_Freight_Instructions	varchar(500), 
@SO_SHIPPING_INSTRUCTIONS_ID   	int,
@SO_Freight_Instructions_Id 	int,
@SO_Letter_Of_Credit_No		varchar(50),
@SO_LC_Issuing_Bank		varchar(500),
@SO_ENDCUSTOMER_ID	        int,
@SO_NEW_END_USER		VARCHAR(50),
@SO_NEW_END_CUSTOMER		VARCHAR(50),
@SO_Quotation_No		VARCHAR(50),
@SO_USER_FIELD1            VARCHAR(100),
@SO_USER_FIELD2            VARCHAR(100)

AS

declare @db_SO_Customer_PO_Number varchar(50),@db_SO_Contact_Person varchar(50),@db_SO_Sales_Person varchar(50)
declare @db_SO_Shipping_Method varchar(50),@db_SO_Shipping_Account varchar(50)
declare @db_SO_Shipping_Address_ID int
declare @db_SO_Comments varchar(500),@db_SO_Shipping_Instructions varchar(500),@db_SO_EndUser varchar(50)
declare @db_SO_Sales_Tax_Type varchar(50)
declare @db_SO_Sub_Total float, @db_SO_Sales_Tax float, @db_SO_Frieght_Tax float, @db_SO_Misc float
declare @db_SalesOrder_No varchar(50),@db_Regional_Rep varchar(50), @db_Shipping_Terms varchar(50)
declare @db_Delivery_Terms varchar(50), @db_SO_Booking_date datetime
declare @db_SalesOrder_ID int 
declare @db_Customer_ID int 
declare @db_CSO_Reason varchar(50)
declare @db_SO_Misc_Name varchar(30) 
declare @db_Billing_Address_Id int
declare @oldPhone varchar(50)
declare @oldFax varchar(50)
declare @oldEmail varchar(50)
declare @oldCellNumber varchar(50)
declare @db_Customer_Name varchar(65)
declare @db_SO_Billing_Contact_Person varchar(50)
declare @db_SO_Shipping_Contact_Person varchar(50) 
declare @db_SO_Sales_Rep varchar(50)
declare @db_SO_PO_Rev	  varchar(50) 
declare @db_SO_PO_Date	  datetime 
declare @db_SO_ODM_ID    int
declare @db_SO_Is_Customer_Ship_Address bit
 
DECLARE @db_SO_Freight_Instructions 	varchar(500)  
declare @db_SO_SHIPPING_INSTRUCTIONS_ID int
DECLARE @db_SO_Freight_Instructions_Id 	int
DECLARE @db_SO_Letter_Of_Credit_No	varchar(50)
DECLARE @db_SO_LC_Issuing_Bank		varchar(500) 
DECLARE @DB_SO_ENDCUSTOMER_ID    	INT
DECLARE @DB_SO_NEW_END_USER		VARCHAR(50)
DECLARE @DB_SO_NEW_END_CUSTOMER		VARCHAR(50)
DECLARE @DB_Quotation_NO		VARCHAR(50)
DECLARE @DB_SO_USER_FIELD1         VARCHAR(100)
DECLARE @DB_SO_USER_FIELD2         VARCHAR(100)

 	--All the select statements removed and a single select is used
 	SELECT @db_SO_Customer_PO_Number = SO_Customer_PO_Number, @db_SO_Contact_Person = SO_Contact_Person,
 		@db_SO_Sales_Person = SO_Sales_Person,@db_SO_Shipping_Method =  SO_Shipping_Method, 
 		@db_SO_Shipping_Account = SO_Shipping_Account,@db_SO_Shipping_Address_ID = SO_Shipping_Address_ID,
 		@db_SO_Comments =  SO_Comments, @db_SO_Shipping_Instructions = SO_Shipping_Instructions, 
 		@db_SO_EndUser = SO_EndUser,@db_SO_Sales_Tax_Type =  SO_Sales_Tax_Type, 
 		@db_SO_Sub_Total =  SO_Sub_Total, @db_SO_Sales_Tax =  SO_Sales_Tax, 
 		@db_SO_Frieght_Tax =  SO_Frieght_Tax, @db_SO_Misc =  SO_Misc,
 		@db_SO_Misc_Name= SO_Misc_Name,
 		@db_SalesOrder_No =  SalesOrder_No,@db_Regional_Rep =  Regional_Rep, 
 		@db_Shipping_Terms =  Shipping_Terms, @db_Delivery_Terms =  Delivery_Terms, 
 		@db_SO_Booking_date =  SO_Booking_date,@db_SalesOrder_ID =  SalesOrder_ID ,
 		@db_Billing_Address_Id = SO_Billing_Address_Id , @db_Customer_ID = Customer_Id,  --Added by Sarada 12/12/2003 --
         	@db_SO_Billing_Contact_Person = ISNULL( SO_Billing_Contact_Person, '' ) , @db_SO_Shipping_Contact_Person = ISNULL( SO_Shipping_Contact_Person, '' ),  -- Added by veeru (17-05-04)
		@db_SO_Sales_Rep = SO_Sales_Rep, @db_SO_PO_Rev = SO_PO_Rev, @db_SO_PO_Date = SO_PO_Date, @db_SO_ODM_ID = SO_ODM_ID, @db_SO_Is_Customer_Ship_Address = @SO_Is_Customer_Ship_Address, @db_SO_Freight_Instructions = SO_Freight_Instructions,
                @db_SO_SHIPPING_INSTRUCTIONS_ID = SO_SHIPPING_INSTRUCTIONS_ID, @db_SO_Freight_Instructions_Id = SO_Freight_Instructions_Id,
		@db_SO_Letter_Of_Credit_No = ISNULL( SO_Letter_Of_Credit_No, '' ), @db_SO_LC_Issuing_Bank = ISNULL( SO_LC_Issuing_Bank, '' ),
		@DB_SO_ENDCUSTOMER_ID = SO_ENDCUSTOMER_ID, @DB_SO_NEW_END_USER = SO_NEW_END_USER, @DB_SO_NEW_END_CUSTOMER = SO_NEW_END_CUSTOMER,
		@DB_Quotation_NO = Quotation_No, @DB_SO_USER_FIELD1 = SO_USER_FIELD1, @DB_SO_USER_FIELD2 = SO_USER_FIELD2
 		FROM Sales_Order WHERE SalesOrder_ID = @salesOrder_id

	SELECT @db_SO_Freight_Instructions = Freight_Instructions FROM Freight_Instructions WHERE FI_ID = @db_SO_Freight_Instructions_Id
 	SELECT @oldPhone = Phone_Number ,@oldFax = Fax_Number , @oldEmail = Email , @oldCellNumber = @CellNumber FROM Address WHERE Address_Id = @db_Billing_Address_Id
        SELECT @db_Customer_Name = C_Name FROM Customer WHERE Customer_Id = @db_Customer_ID
 	
 	declare @Comments_Flag varchar(10)
 	declare @Shipping_Flag varchar(10)
 	if((@SO_Comments <> @db_SO_Comments) or (@db_SO_Comments is NULL))
 	begin
 		set @Comments_Flag = 'TRUE'
 	end
 	else
 	begin
 		set @Comments_Flag = 'FALSE'
 	end
 	if((@SO_Shipping_Instructions <> @db_SO_Shipping_Instructions) or (@db_SO_Shipping_Instructions is NULL))
 	begin
 		set @Shipping_Flag = 'TRUE'
 	end
 	else
 	begin
 		set @Shipping_Flag = 'FALSE'
 	end
 	/*
 	 * Check if there is any change in the sales order 
 	 */
 	set @Status_Flag = 0

 	declare @change_number int
 	SELECT @change_number = count(ChangeNo) FROM Change_Sales_Order WHERE SalesOrder_ID = @salesorder_id
 	if @change_number = 0
 	begin
 		INSERT INTO Change_Sales_Order(SalesOrder_ID,ChangeNo,Customer_ID,CSO_Customer_PO_Number,CSO_Contact_Person,
 		CSO_Sales_Person,CSO_Shipping_Method,CSO_Shipping_Account,CSO_Billing_Address_ID,CSO_Shipping_Address_ID,
 			CSO_Comments,CSO_Shipping_Instructions,CSO_EndUser,CSO_Status,CSO_Booking_Date,CSO_Is_Sample,CSO_Is_Cancel,
 			CSO_Is_Pending,CSO_Is_Shipped,CSO_Entered_By_ID,CSO_Entered_By_Name,CSO_Sales_Tax_Type,CSO_Sub_Total,
 			CSO_Sales_Tax,CSO_Frieght_Tax,CSO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
 			CSO_Misc_Name,CSO_Show_Pro_Forma_Invoice,CSO_Billing_Contact_Person,CSO_Shipping_Contact_Person,
			CSO_Sales_Rep, CSO_PO_Rev, CSO_PO_Date,CSO_Is_Customer_Ship_Address,CSO_Freight_Instructions,
			CSO_SHIPPING_INSTRUCTIONS_ID, CSO_Freight_Instructions_Id, CSO_Letter_Of_Credit_No, CSO_LC_Issuing_Bank, CSO_NEW_END_USER, CSO_NEW_END_CUSTOMER, Quotation_No, CSO_USER_FIELD1, CSO_USER_FIELD2)
 		
 			(SELECT SalesOrder_ID,@change_number,Customer_ID,SO_Customer_PO_Number,SO_Contact_Person,
 			SO_Sales_Person,SO_Shipping_Method,SO_Shipping_Account,SO_Billing_Address_ID,SO_Shipping_Address_ID,
 			SO_Comments,SO_Shipping_Instructions,SO_EndUser,SO_Status,SO_Booking_Date,SO_Is_Sample,SO_Is_Cancel,
 			SO_Is_Pending,SO_Is_Shipped,SO_Entered_By_ID,SO_Entered_By_Name,SO_Sales_Tax_Type,SO_Sub_Total,
 			SO_Sales_Tax,SO_Frieght_Tax,SO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
 			@miscName,SO_Show_Pro_Forma_Invoice,SO_Billing_Contact_Person,SO_Shipping_Contact_Person,SO_Sales_Rep, 
			SO_PO_Rev, SO_PO_Date, SO_Is_Customer_Ship_Address, SO_Freight_Instructions,SO_SHIPPING_INSTRUCTIONS_ID, SO_Freight_Instructions_Id,
			SO_Letter_Of_Credit_No, SO_LC_Issuing_Bank, SO_NEW_END_USER, SO_NEW_END_CUSTOMER, Quotation_No, SO_USER_FIELD1, SO_USER_FIELD1
 			FROM Sales_Order WHERE SalesOrder_id = @salesorder_id)
 	end

	DECLARE @IS_SAVE_END_CUST_AND_END_USER VARCHAR(10)
	SET @IS_SAVE_END_CUST_AND_END_USER = 'FALSE'
	SELECT @IS_SAVE_END_CUST_AND_END_USER = PROPERTY_VALUE FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'SCALAR.CHIPTRACK.SALES.SALESORDER.ISSAVEENDCOSTOMERANDENDUSER'
	IF ( @IS_SAVE_END_CUST_AND_END_USER = 'TRUE' )
	BEGIN
		IF ( @SO_NEW_END_USER IS NOT NULL AND LEN(RTRIM(LTRIM(@SO_NEW_END_USER))) > 1 )
		BEGIN
			EXEC SP_CREATE_NEW_CUSTOMER @SO_NEW_END_USER, 'End User'
			SELECT @SO_ENDCUSTOMER_ID = CUSTOMER_ID FROM Customer WHERE C_Name = @SO_NEW_END_USER
			IF @SO_ENDCUSTOMER_ID > 1
			BEGIN
				INSERT INTO Customer_Endcustomer_Relationship (ParentCustomer_Id, EndCustomer_ID, RelationshipStatus_Id)
				VALUES ( @db_Customer_ID, @SO_ENDCUSTOMER_ID, 1 )
				SET @SO_NEW_END_USER = ''
			END
		END
		IF ( @SO_NEW_END_CUSTOMER IS NOT NULL AND LEN(RTRIM(LTRIM(@SO_NEW_END_CUSTOMER))) > 1 )
		BEGIN
			EXEC SP_CREATE_NEW_CUSTOMER @SO_NEW_END_CUSTOMER, 'End Customer' 
			SELECT @SO_ODM_ID = CUSTOMER_ID FROM Customer WHERE C_Name = @SO_NEW_END_CUSTOMER
			
			IF @SO_ODM_ID > 1
			BEGIN
				INSERT INTO Customer_ODM_Relationship (ParentCustomer_Id, ODMCustomer_ID, RelationshipStatus_Id)
				VALUES ( @db_Customer_ID, @SO_ODM_ID, 1 )
				SET @SO_NEW_END_CUSTOMER = ''
			END
		END
	END

	
 	if (@db_SO_Customer_PO_Number<> @SO_Customer_PO_Number or @db_SO_Contact_Person<>@SO_Contact_Person or
 	@db_SO_Sales_Person <> @SO_Sales_Person or @db_SO_Shipping_Method <> @SO_Shipping_Method or
 	@db_SO_Shipping_Account <> @SO_Shipping_Account or @db_Billing_Address_Id <> @SO_Billing_Address_ID or
 	@db_SO_Shipping_Address_ID <> @SO_Shipping_Address_ID or @db_SO_Comments <>@SO_Comments or
 	@db_SO_Shipping_Instructions <> @SO_Shipping_Instructions or @db_SO_EndUser <> @SO_EndUser or
 	@db_SO_Sales_Tax_Type <> @SO_Sales_Tax_Type or 	@db_SO_Sub_Total <> @SO_Sub_Total or
 	@db_SO_Sales_Tax<> @SO_Sales_Tax or @db_SO_Frieght_Tax <> @SO_Frieght_Tax or
 	@db_SO_Misc <> @SO_Misc or @db_Regional_Rep <> @Regional_Rep or
 	@db_Shipping_Terms <> @Shipping_Terms or @db_Delivery_Terms <> @Delivery_Terms or
 	@db_SO_Booking_date <> @SO_Booking_Date or @db_CSO_Reason <> @CSO_Reason or @db_SO_Misc_Name <> @miscName or
 	@oldPhone <> @Phone or @oldFax <> @Fax or @oldEmail <> @Email or @oldCellNumber <> @CellNumber or
        @db_SO_Billing_Contact_Person<>@SO_Billing_Contact_Person or @db_SO_Shipping_Contact_Person<>@SO_Shipping_Contact_Person or
	@db_SO_Sales_Rep <> @SO_Sales_Rep or @db_SO_PO_Rev <> @SO_PO_Rev or ISNULL ( @db_SO_PO_Date, '' ) <> ISNULL ( @SO_PO_Date, '' ) or ISNULL ( @db_SO_ODM_ID, '' ) <> ISNULL ( @SO_ODM_ID, '' ) or 
        @db_SO_Is_Customer_Ship_Address <> @SO_Is_Customer_Ship_Address OR @db_SO_Freight_Instructions <> @SO_Freight_Instructions
	 or @db_SO_Letter_Of_Credit_No <> @SO_Letter_Of_Credit_No or @db_SO_LC_Issuing_Bank <> @SO_LC_Issuing_Bank or ISNULL( @db_SO_ENDCUSTOMER_ID, '' ) <>  ISNULL( @SO_ENDCUSTOMER_ID, '' )
	OR ISNULL( @DB_SO_NEW_END_USER, '' ) <>  ISNULL( @SO_NEW_END_USER, '' ) OR ISNULL( @DB_SO_NEW_END_CUSTOMER, '' ) <>  ISNULL( @SO_NEW_END_CUSTOMER, '' ) OR ISNULL( @SO_Quotation_No, '' ) <>  ISNULL( @DB_Quotation_NO, '' )
    OR ISNULL( @SO_USER_FIELD1, '' ) <>  ISNULL( @DB_SO_USER_FIELD1, '' ) OR ISNULL( @SO_USER_FIELD2, '' ) <>  ISNULL( @SO_USER_FIELD2, '' )
    )
 	begin
 		UPDATE SALES_ORDER SET 
 		SO_Customer_PO_Number = @SO_Customer_PO_Number,
 		SO_Contact_Person =@SO_Contact_Person,
 		SO_Sales_Person = @SO_Sales_Person,
 		SO_Shipping_Method = @SO_Shipping_Method,
 		SO_Shipping_Account = @SO_Shipping_Account,
 		SO_Billing_Address_ID = @SO_Billing_Address_ID,
 		SO_Shipping_Address_ID = @SO_Shipping_Address_ID,
 		SO_Comments = @SO_Comments,
 		SO_Shipping_Instructions = @SO_Shipping_Instructions,
 		SO_EndUser = @SO_EndUser,
 		SO_Sales_Tax_Type = @SO_Sales_Tax_Type,
 		SO_Sub_Total = @SO_Sub_Total,
 		SO_Sales_Tax = @SO_Sales_Tax,
 		SO_Frieght_Tax = @SO_Frieght_Tax,
 		SO_Misc = @SO_Misc,
 		SalesOrder_No = @SalesOrder_No,
		Timestamp = CURRENT_TIMESTAMP,
 		Regional_Rep = @Regional_Rep,
 		Shipping_Terms = @Shipping_Terms,
 		Delivery_Terms = @Delivery_Terms,
 		SO_Booking_date = @SO_Booking_date,
 		SO_Misc_Name = @miscName,
 		SO_Billing_Contact_Person = @SO_Billing_Contact_Person,
 		SO_Shipping_Contact_Person = @SO_Shipping_Contact_Person,
		SO_Sales_Rep = @So_Sales_Rep,
		SO_PO_Rev = @SO_PO_Rev,
		SO_PO_Date = @SO_PO_Date,	
		SO_ODM_ID = @SO_ODM_ID,
		SO_Is_Customer_Ship_Address = @SO_Is_Customer_Ship_Address,
 		-- end
		SO_Freight_Instructions = @SO_Freight_Instructions,
		SO_SHIPPING_INSTRUCTIONS_ID = @SO_SHIPPING_INSTRUCTIONS_ID,
		SO_Freight_Instructions_Id  = @SO_Freight_Instructions_Id,
		SO_Letter_Of_Credit_No = @SO_Letter_Of_Credit_No,	
		SO_LC_Issuing_Bank     = @SO_LC_Issuing_Bank,
		SO_ENDCUSTOMER_ID = @SO_ENDCUSTOMER_ID,
		SO_NEW_END_USER = @SO_NEW_END_USER,
		SO_NEW_END_CUSTOMER = @SO_NEW_END_CUSTOMER,
		Quotation_No		= @SO_Quotation_No,
        SO_USER_FIELD1      = @SO_USER_FIELD1,
        SO_USER_FIELD2      = @SO_USER_FIELD2
 		WHERE SalesOrder_ID = @salesorder_id
 	
 	end
 
 	if( @SO_Contact_Person = @db_Customer_Name )
 	begin
 		declare @Temp_Id int
 		SELECT @Temp_Id = count(SalesOrder_Id)FROM Sales_Order_Address WHERE  SalesOrder_ID = @salesorder_id 
 		if ( @temp_Id = 0 )
 			begin
 				INSERT INTO Sales_Order_Address (Phone_Number,Fax_Number,Email,SalesOrder_ID) values(@Phone,@Fax,@Email,@salesorder_id) 
 			end
 		else
 			begin	
 				UPDATE Sales_Order_Address SET Phone_Number = @Phone, Fax_Number = @Fax , Email = @Email 
 				WHERE  SalesOrder_ID = @salesorder_id 
 			end
 	end
 
 	else
        begin
 		UPDATE Customer_Contact_Persons_Table  SET Phone_Number = @Phone, Fax= @Fax , Email = @Email , Cell_Number =  @CellNumber , Address = @Address
 		WHERE  Customer_Id = @db_Customer_ID and Contact_person = @SO_Contact_Person
 	end
return
